import xlsxwriter
from io import BytesIO
from apis import api_routes
from sqlalchemy import text
from typing import List
from typing import Union
from urllib.parse import unquote, quote
from security.decoration import certification
from flask import g, request, jsonify, Response, abort
from public.local import local_time
from models.facility.facility import Facility
from models.facility.status import Status
from datetime import datetime, timedelta
from models.owner.owner import Owner


@api_routes.route("/status/kind", methods=["GET"])
def func_status_facility_get_api():
    """
    查询设备总的运行状态种类， 完成API开发
    http://127.0.0.1:8080/api/status/kind
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 设备状态模块
    """
    resp: list = [
        "未运行",
        "初始化中",
        "初始化完成",
        "自动运行中",
        "设备报警中",
    ]
    return jsonify({'status': 200, 'data': resp})


@api_routes.route("/status", methods=["GET"])
def func_status_list_get_api():
    """
    设备运行状态记录查询, 只要有token 都可以访问, API 开发完成，对接PLC未完成 完成API开发
    组合方式, 一共4种，其他组合默认第一种
    http://127.0.0.1:8080/api/status
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "index"         int      ****  第几页，默认为1
    "size"          int      ****  每页数据多少, 默认每页10条数据
    "facility_id"   int            设备ID
    "start"         string         开始日期
    "finish"        string         结束日期
    "owner_id"      Int            默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    "name"          string         运行状态类型
    }
    :return:
        {'status': 400, 'data': data}
    ---
    tags:
      - 设备状态模块
    """
    index = request.args.get("index", default=1, type=int)
    size = request.args.get("size", default=10, type=int)
    ft_id: int = request.args.get("facility_id", default=None, type=int)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)
    owner_id: int = request.args.get("owner_id", default=0, type=int)
    name: Union[str, None] = request.args.get("name", default=None, type=str)

    if owner_id not in [0, 1, 2]:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return jsonify({'status': 4001, 'msg': "选择班次不能跨天查询或未指定日期"})

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    parm1, parm2, parm3, parm4 = "", "", "", ""
    if ft_id is not None:
        parm1 = " AND status.facility_id = :ft_id "
    if start is not None:
        parm2 = " AND status.time >= :start "
    if finish is not None:
        parm3 = " AND status.time <= :finish "
    if name is not None and len(name) >= 1:
        parm4 = " AND status.message = :message "

    parm: dict = {
        "ft_id": ft_id, "start": start,
        "finish": finish, "message": name,
        "arg2": size, "arg3": ((index - 1) * size)
    }

    res: list = []
    sql_count = """
        SELECT
            COUNT(status.id)
        FROM status where status.id > 0 {} {} {} {}
    """

    sql_count = sql_count.format(parm1, parm2, parm3, parm4)
    counts, = g.session.execute(text(sql_count), parm).fetchone()
    page: int = int((counts / size) + 1) if (counts / size) > int(counts / size) else int(counts / size)

    sql_date = """
        SELECT
            coalesce(to_char(detailed_fst.time,'yyyy-MM-dd hh24:MI:ss'), null) AS start,
            coalesce(to_char(detailed_fst.finish,'yyyy-MM-dd hh24:MI:ss'), null) AS finish,
            facility.name, facility.serials, detailed_fst.code, detailed_fst.message, detailed_fst.account, detailed_fst.expenditure
        FROM
        (
            SELECT
                status.*
            FROM status WHERE status.id > 0 {} {} {} {} ORDER BY status.id LIMIT :arg2 OFFSET :arg3
        ) AS detailed_fst
        INNER JOIN facility ON detailed_fst.facility_id = facility.id
        ORDER BY detailed_fst.id
    """
    sql_date = sql_date.format(parm1, parm2, parm3, parm4)
    db_data = g.session.execute(text(sql_date), parm).fetchall()

    for itr in db_data:
        res.append({
            "start": itr[0], "finish": itr[1], "ft_name": itr[2],
            "serials": itr[3], "code": itr[4], "message": itr[5], "account": itr[6], "expenditure": itr[7],
        })
    doc: dict = {
        "start": "开始时间", "finish": "结束时间", "ft_name": "设备名",
        "serials": "设备编号", "code": "状态码", "message": "状态模式", "account": "操作用户", "expenditure": '持续时间'
    }
    return jsonify({'status': 200, 'data': res, "index": index, "size": size, "total": counts, "pages": page, "doc": doc})


@api_routes.route("/status/output", methods=["GET"])
def func_status_list_output_get_api():
    """
    设备运行状态记录导出EXCEL API, 完成API开发
    组合方式, 一共4种，其他组合默认第一种
    http://127.0.0.1:8080/api/status/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "file"          string         导出文件名，没有默认按照今天日期
    "facility_id"   int            设备ID
    "start"         string         开始日期
    "finish"        string         结束日期
    "owner_id"      Int            默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    "name"          string         运行状态类型
    }
    :return:
        数据流
    ---
    tags:
      - 设备状态模块
    """
    ft_id: int = request.args.get("facility_id", default=None, type=int)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)
    owner_id: int = request.args.get("owner_id", default=0, type=int)
    name: Union[str, None] = request.args.get("name", default=None, type=str)

    if owner_id not in [0, 1, 2]:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return jsonify({'status': 4001, 'msg': "选择班次不能跨天查询或未指定日期"})

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    parm1, parm2, parm3, parm4 = "", "", "", ""
    if ft_id is not None:
        parm1 = " AND status.facility_id = :ft_id "
    if start is not None:
        parm2 = " AND status.time >= :start "
    if finish is not None:
        parm3 = " AND status.time <= :finish "
    if name is not None and len(name) >= 1:
        parm4 = " AND status.message = :message "

    parm: dict = {
        "ft_id": ft_id, "start": start,
        "finish": finish, "message": name
    }
    sql_date = """
        SELECT
            coalesce(to_char(detailed_fst.time,'yyyy-MM-dd hh24:MI:ss'), null) AS start,
            coalesce(to_char(detailed_fst.finish,'yyyy-MM-dd hh24:MI:ss'), null) AS finish,
            facility.name, facility.serials, detailed_fst.code, detailed_fst.message, detailed_fst.account, detailed_fst.expenditure
        FROM
        (
            SELECT
                status.*
            FROM status WHERE status.id > 0 {} {} {} {} ORDER BY status.id
        ) AS detailed_fst
        INNER JOIN facility ON detailed_fst.facility_id = facility.id
        ORDER BY detailed_fst.id
    """
    sql_date = sql_date.format(parm1, parm2, parm3, parm4)
    db_data = g.session.execute(text(sql_date), parm).fetchall()
    file_name = "{}.xlsx".format(local_time())

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet_index = workbook.add_worksheet("设备历史运行状态数据表")
    worksheet_index.set_column("A:ZZ", 32)
    message: list = ["设备名字", "设备编号", "操作用户", "开始时间", "状态码", "状态描述", "结束时间", "时间/秒"]

    worksheet_index.write_row(row=0, col=0, data=message)
    for index, itr in enumerate(db_data):
        sequence = index + 1
        worksheet_index.write_row(row=sequence, col=0, data=[itr[2], itr[3], itr[6], itr[0], itr[4], itr[5], itr[1], itr[7]])

    # 在数据流之前关闭工作薄
    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200


@api_routes.route("/status/statistics", methods=["GET"])
def func_status_statistics_get_api():
    """
    设备运行状态记录统计, 只要有token 都可以访问, API
    http://127.0.0.1:8080/api/status/statistics
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "facility_id"   int            设备ID
    "start"         string         开始日期, 格式 %Y-%m-%d
    "finish"        string         结束日期, 格式 %Y-%m-%d
    "owner_id"      Int            默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    "name"          string  ****   运行状态类型
    }
    :return:
        数据流
    ---
    tags:
      - 设备状态模块
    """
    ft_id: Union[int, None] = request.args.get("facility_id", default=None, type=int)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)
    owner_id: int = request.args.get("owner_id", default=0, type=int)
    name: Union[str, None] = request.args.get("name", default=None, type=str)

    if owner_id not in [0, 1, 2] or name is None or len(name) == 0:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None
    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return jsonify({'status': 4001, 'msg': "选择班次不能跨天查询或未指定日期"})

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    sql_date = """
        SELECT
            facility.name, facility.workplace,
            facility.serials, andon_st.numbers, andon_st.totals
        FROM
        (
            SELECT
                facility_id, COUNT(facility_id) AS numbers, SUM(expenditure) AS totals
            FROM status WHERE message = :name {} GROUP BY facility_id
        ) AS andon_st
        INNER JOIN facility ON andon_st.facility_id = facility.id ORDER BY facility.id
    """
    change = False
    if ft_id is not None and start is None and finish is None:
        change: bool = True
        sql_date = sql_date.format("AND facility_id = {}".format(ft_id))
    elif ft_id is not None and start is not None and finish is not None:
        change: bool = True
        temp: str = "AND facility_id = {} AND time > '{}' AND time < '{}'"
        sql_date = sql_date.format(temp.format(ft_id, start, finish))
    elif ft_id is None and start is not None and finish is not None:
        change: bool = True
        temp: str = "AND time > '{}' AND time < '{}'"
        sql_date = sql_date.format(temp.format(start, finish))
    if not change:
        sql_date = sql_date.format("")
    db_data = g.session.execute(text(sql_date), {"name": name}).fetchall()

    data: list = []
    parm: Union[str, None] = None
    db_fts: List[Facility] = g.session.query(Facility).filter(Facility.uuid4 != parm).order_by(Facility.id).all()
    for index0, itr0 in enumerate(db_fts):
        init_awaits_numbers: int = 0
        init_awaits_totals: int = 0
        for ft_nm, ft_wk, ft_s2, as_ns, as_ts in db_data:
            if ft_s2 == itr0.serials:
                init_awaits_numbers = as_ns
                init_awaits_totals = as_ts
                break
        if init_awaits_totals is None:
            init_awaits_totals = 0

        hour_format = int(init_awaits_totals / 3600)
        mount_format = int((init_awaits_totals - (3600 * hour_format)) / 60)
        se_format = int(init_awaits_totals - (3600 * hour_format) - (60 * mount_format))
        time_format = "{}时{}分{}秒".format(hour_format, mount_format, se_format)

        data.append({
            "facility_name": itr0.name,
            "facility_workplace": itr0.workplace,
            "facility_serials": itr0.serials,
            "status_numbers": init_awaits_numbers,
            "status_totals": init_awaits_totals,
            "time_format": time_format,
            "name": name
        })
    docs: dict = {
        "facility_name": "设备名称",
        "facility_workplace": "设备位置",
        "facility_serials": "设备序列号",
        "status_numbers": "发生次数",
        "status_totals": "时间/单位秒",
        "time_format": "秒转时间格式",
        "name": "运行状态"
    }
    return jsonify({'status': 200, 'data': data, "docs": docs})

